package dblayer;

import java.sql.*;
import java.util.ArrayList;
import modellayer.PartOrder;
import modellayer.Product;

/**
 * 
 * @author Gruppe 3
 *
 */
public class DBPartOrder implements IFDBPartOrder {
	
	private Connection con;

	public DBPartOrder()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	/**
 	 * An ArrayList with all PartOrders
 	 */
	public ArrayList<PartOrder> getAllPartOrders(boolean retriveAssociation) {
		return miscWhere("", retriveAssociation);
	}
	
	/**
 	 * An ArrayList with all PartOrders
 	 */
	public ArrayList<PartOrder> getAllPartOrdersByOrder(int salesOrderID, boolean retriveAssociation) {
		return miscWhere(" salesOrderID = "+salesOrderID, retriveAssociation);
	}

	/**
     * Select a single PartOrder
     */
	public PartOrder selectSinglePartOrder(int PartOrderID,
			boolean retriveAssociation) throws SQLException {
		String wClause = "partOrderID = "+PartOrderID;
        return singleWhere(wClause, retriveAssociation);
	}

    /**
     * Insert a PartOrder to the DB
     */
    public int insertPartOrder(PartOrder p) throws SQLException 
    {
   	 PreparedStatement pstmt = null;
   	 int controlInt = -1;
   	 String insert = "insert into PartOrder(amount, productID, salesOrderID)"
                      + "values (?, ?, ?)";
   	 //System.out.println(insert);
   	 try {
   		 pstmt = con.prepareStatement(insert);
   		 pstmt.setInt(1, p.getAmount());
   		 pstmt.setInt(2, p.getProductID());
         pstmt.setInt(3, p.getSalesOrderID());
         controlInt = pstmt.executeUpdate();
        } catch (SQLException sqlE) {
            System.out.println("SQL Error, PartOrder not inserted");
            System.out.println(sqlE.getMessage());
        } catch (Exception e) {
            e.getMessage();
        }
   	 
   	 return controlInt;
    }
    
    /**
     * Updates the PartOrder db
     */
    @Override
    public int updatePartOrder(PartOrder p) throws SQLException {
   	 
   	 int controlInt = -1;

   	 String update = "UPDATE PartOrder SET "
   	 		+ "amount = ? "
   	 		+ "productID = ?"
   	 		+ "salesOrderID = ?"
   	 		+ "WHERE partOrderID = ?";

   	 //System.out.println(update);
   	 try {
   		 PreparedStatement pstmt = null;
   		 pstmt = con.prepareStatement(update);
   		 pstmt.setInt(1, p.getAmount());
   		 pstmt.setInt(2, p.getProductID());
         pstmt.setInt(3, p.getSalesOrderID());
   		 controlInt = pstmt.executeUpdate();
   	 } catch (SQLException sqlE) {
   		 System.out.println("SQL Error, PartOrder not updated");
   		 System.out.println(sqlE.getMessage());
   	 } catch (Exception e) {
   		 e.getMessage();
   	 }
   	 
   	 return controlInt;
    }
    
    /**
     * Delete a PartOrder from the database
     */
    public int deletePartOrder(PartOrder p) throws SQLException 
    {
   	 PreparedStatement pstmt = null;
   	 int controlInt = -1;
   	 
   	 String delete = "DELETE FROM PartOrder WHERE partOrderID = ?";
   	 //System.out.println(delete);
   	 try {
   		 pstmt = con.prepareStatement(delete);
            pstmt.setInt(1, p.getPartOrderID());
            controlInt = pstmt.executeUpdate();
        } catch (SQLException sqlE) {
            System.out.println("SQL Error, PartOrder not deleted");
            System.out.println(sqlE.getMessage());
        } catch (Exception e) {
            e.getMessage();
        }
   	 
   	 return controlInt;
    }
    
    /**
	 * If more than one PartOrder is to be selected
	 * @param wClause
	 * @return ArrayList with PartOrder objects
	 */
    private ArrayList<PartOrder> miscWhere(String wClause, boolean retrieveAssociation)
	 {
   	 ResultSet results;
	     ArrayList<PartOrder> list = new ArrayList<PartOrder>();	
		
	     String query = buildQuery(wClause);
   
	     try{ 
			 Statement stmt = con.createStatement();
		 	 stmt.setQueryTimeout(5);
		 	 results = stmt.executeQuery(query);
		 	
			 while( results.next() ){ 
			     PartOrder pObj = new PartOrder();
			     pObj = buildPartOrder(results);	
		         list.add(pObj);	
			 }//end while
			
	         stmt.close();
	         
	        if(retrieveAssociation) {
	        	for(PartOrder pObj : list) {
					IFDBProduct dbProduct = new DBProduct();
					int productID = pObj.getProductID();
					Product productObj = dbProduct.selectSingleProduct(productID, false);
					pObj.setProduct(productObj); //set the product object in the partOrder object
	        	}//end for
			}//end if
		 }//end try
	 	 catch(Exception e){
	 	 	System.out.println("Query exception - select: "+e);
		 	e.printStackTrace();
	 	 }
		 return list;
	 }
    
    /**
	 * If only one product is to be selected
	 * @param wClause
	 * @return PartOrder object
	 */
	 private PartOrder singleWhere(String wClause, boolean retrieveAssociation)
	 {
	 	ResultSet results;
	 	PartOrder pObj = new PartOrder();
		        
	 	String query = buildQuery(wClause);
		//System.out.println(query);
		
		try{ 	
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if( results.next() ){
				pObj = buildPartOrder(results);
				stmt.close();
				
				if(retrieveAssociation) {
					IFDBProduct dbProduct = new DBProduct();
					int productID = pObj.getProductID();
					Product productObj = dbProduct.selectSingleProduct(productID, retrieveAssociation);
					pObj.setProduct(productObj); //set the product object in the partOrder object
				}
				
			}else{ //No partOrder found
				pObj = null;
			}
		}//end try	
		
		catch(Exception e){
			System.out.println("Query exception: "+e);
		}
		return pObj;
	 }
	 
	 /**
	 * Method to build the query
	 * @param wClause
	 * @return
	 */
	 private String buildQuery(String wClause)
	 {
	     String query="SELECT * FROM PartOrder";
		
	 	if (wClause.length()>0)
	 		query=query+" WHERE "+ wClause;
	 		
	 	return query;
	 }
    
    /**
     * Builds a PartOrder object from the data in the database
     * @param result
     * @return PartOrder object
     * @throws SQLException
     */
    private PartOrder buildPartOrder(ResultSet result) throws SQLException {
   	 PartOrder pObj = new PartOrder();

   	 try {
   		 pObj.setPartOrderID(result.getInt("partOrderID"));
   		 pObj.setAmount(result.getInt("amount"));
   		 pObj.setSalesOrderID(result.getInt("salesOrderID"));
   		 pObj.setProductID(result.getInt("productID"));
   	 } catch (Exception e) {
   		 System.out.println("building PartOrder object");
        }
        return pObj;
    }


}
